﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.IO;

namespace slaChartTestWindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        #region Constructors

        public Form1()
        {
            InitializeComponent();
        }

        #endregion

        #region App Settings

        static public string GetAppSetting(String mName)
        {
            String vStr = null;

            string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(strPath);
            XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
            foreach (XmlNode keynode in node.ChildNodes)
            {
                string keyStr = keynode.Attributes["key"].Value;
                if (keyStr == mName)
                {
                    vStr = keynode.Attributes["value"].Value;
                    break;
                }
            }

            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }

        #endregion App Settings

        private void btnTest_Click(object sender, EventArgs e)
        {
            DataTable PointsList = new DataTable("tbl");
            XmlDocument xReturn = new XmlDocument();
            Exception err = GetCheckPointsList(ref PointsList);
            if(err != null)
            {
                MessageBox.Show("Error - " + err.Message);
                return;
            }

            using (StringWriter stringWriter = new StringWriter())
            {
                using (XmlTextWriter xmlTextwriter = new XmlTextWriter(stringWriter))
                {
                    PointsList.WriteXml(xmlTextwriter, XmlWriteMode.WriteSchema);
                    //XmlNode xRoot = xReturn.CreateElement("root");
                    //xReturn.AppendChild(xRoot);
                    xReturn.LoadXml(stringWriter.ToString());

                    foreach (XmlNode node0 in xReturn.ChildNodes)
                    {
                        foreach (XmlNode node1 in node0.ChildNodes)
                        {
                            if (node1.Name == "tbl")
                            {
                                foreach (XmlNode node2 in node1.ChildNodes)
                                {
                                    if (node2.Name == "Name")
                                    {
                                        string shopnameTmp = node2.InnerText;
                                        DataTable tblData = new DataTable("row");
                                        err = GetCheckPointsData(ref tblData, shopnameTmp, 2011, 11);
                                        if (err == null)
                                        {
                                            using (StringWriter stringWriter2 = new StringWriter())
                                            {
                                                using (XmlTextWriter xmlTextwriter2 = new XmlTextWriter(stringWriter2))
                                                {
                                                    tblData.WriteXml(xmlTextwriter2, XmlWriteMode.IgnoreSchema);
                                                    XmlDocument xTmp = new XmlDocument();
                                                    xTmp.LoadXml(stringWriter2.ToString());
                                                    foreach(XmlNode xxx in xTmp.ChildNodes[0].ChildNodes)
                                                    {
                                                        XmlNode rowNode = xReturn.CreateElement("row");
                                                        XmlNode rowItemDay = xReturn.CreateElement("Day");
                                                        rowItemDay.InnerText = xxx.ChildNodes[0].InnerText;
                                                        rowNode.AppendChild(rowItemDay);
                                                        XmlNode rowItemRealIn = xReturn.CreateElement("RealIn");
                                                        rowItemRealIn.InnerText = xxx.ChildNodes[1].InnerText; 
                                                        rowNode.AppendChild(rowItemRealIn);
                                                        node1.AppendChild(rowNode);
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }

                }
            }

            //foreach (DataRow r in PointsList.Rows)
            //{
            //    DataTable tblData = new DataTable();
            //    err = GetCheckPointsData(ref tblData, r[1].ToString(), 2011, 11);
            //}
        }

        Exception GetCheckPointsList(ref DataTable tbl)
        {
            Exception ret = null;
            //string connStr = GetAppSetting("MySqlMainSrvConnect");
            string connStr = GetAppSetting("RetailStatConnect");
            connStr = connStr.Replace(@"RetailStatPwd", @"JKHDF83wdu&EY");
            string sql1 =
@"SELECT ID,Name FROM Shops ORDER BY Name";
            try
            {
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sql1, conn))
                    {
                        using (OleDbDataAdapter a = new OleDbDataAdapter(cmd))
                        {
                            if(tbl==null)
                                tbl = new DataTable();
                            a.Fill(tbl);
                        }
                    }
                }
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }

        Exception GetCheckPointsData(ref DataTable tbl, string Point, int Year, int Month)
        {
            Exception ret = null;
            string connStr = GetAppSetting("RetailStatConnect");
            connStr = connStr.Replace(@"RetailStatPwd", @"JKHDF83wdu&EY");
            string sql1 =
  "SELECT DAY(t.DateCreated) as Day,SUM(t.RealIn) as RealIn FROM Shops s "
+ "INNER JOIN Entries e ON e.ShopID=s.ID INNER JOIN Sensors n ON n.EntryID=e.ID "
+ "INNER JOIN Traffic t ON t.SensorID=n.ID WHERE "
+ "YEAR(t.DateCreated)=" + Year.ToString() + " AND MONTH(t.DateCreated)=" + Month.ToString()
+ " AND s.Name='" + Point + "'"
+ " GROUP BY s.[Name], DAY(t.DateCreated) ";

            try
            {
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sql1, conn))
                    {
                        using (OleDbDataAdapter a = new OleDbDataAdapter(cmd))
                        {
                            if(tbl==null)
                                tbl = new DataTable();
                            a.Fill(tbl);
                        }
                    }
                }
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }

    }
}
